import pymysql


def get_salary():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT salary FROM lagou_table'
    cur.execute(sql)
    datas = cur.fetchall()
    salary = [i['salary'] for i in datas]
    ranges = [5, 10, 15, 20, 25, 30, 35, 40, 45, 50]
    counts = [0] * (len(ranges))  # 初始化数量数组
    salary_ranges = ['0k-5k', '5k-10k', '10k-15k', '15k-20k', '20k-25k', '25k-30k', '30k-35k', '35k-40k', '40k-45k',
                     '45k以上']
    for i in salary:
        max_salary = int(i.split('-')[1].replace('k', ''))
        for index, value in enumerate(ranges):
            if max_salary < value:
                counts[index] += 1
                break
    data = {'salary': salary_ranges, 'count': counts}
    print(data)
    cur.close()
    conn.close()
    return data


def get_salary_City():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT city, COUNT(*) as count FROM lagou_table GROUP BY city order by count DESC LIMIT 10'
    cur.execute(sql)
    datas1 = cur.fetchall()
    city1 = [i['city'] for i in datas1]
    count = [[i['count'], 0] for i in datas1]
    datas = dict(zip(city1, count))

    sql = 'SELECT city, salary FROM lagou_table'
    cur.execute(sql)
    datas2 = cur.fetchall()
    city2 = [i['city'] for i in datas2]
    salary = [i['salary'] for i in datas2]
    for i in range(len(city2)):
        if city2[i] in datas:
            datas[city2[i]][1] += int(str(salary[i]).replace('k', '').split('-')[1]) * 1000

    city = []
    avg_salary = []
    for key, value in datas.items():
        city.append(key)
        avg_salary.append(int(value[1] / value[0]))

    data = {'city': city, 'avg_salary': avg_salary}
    print(data)
    cur.close()
    conn.close()
    return data


def get_positionName():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT positionName, COUNT(*) as count FROM lagou_table GROUP BY positionName ORDER BY count desc LIMIT 15'
    cur.execute(sql)
    datas = cur.fetchall()
    positionName = [i['positionName'] for i in datas]
    counts = [i['count'] for i in datas]
    data = {'positionName': positionName, 'counts': counts}
    print(data)
    cur.close()
    conn.close()
    return data


def get_positionNameCompany():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT companyName, COUNT(*) as count FROM lagou_table GROUP BY companyName ORDER BY count desc LIMIT 10'
    cur.execute(sql)
    datas = cur.fetchall()
    companyName = [i['companyName'] for i in datas]
    counts = [i['count'] for i in datas]
    data = {'companyName': companyName, 'counts': counts}
    print(data)
    cur.close()
    conn.close()
    return data


def get_WordCloud():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT CompanyBenefits FROM lagou_table'
    cur.execute(sql)
    datas = cur.fetchall()
    CompanyBenefits = [str(i['CompanyBenefits']).split('，') for i in datas]
    data1 = {}
    for i in CompanyBenefits:
        for j in i:
            if j in data1:
                data1[j] += 1
            else:
                data1[j] = 1
    data2 = []
    for key, value in data1.items():
        data2.append({'name': key, 'value': value})
    data = {'data': data2}
    print(data)
    cur.close()
    conn.close()
    return data


def get_education():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='jobinformation',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = 'SELECT education, COUNT(*) as count FROM lagou_table GROUP BY education'
    cur.execute(sql)
    datas = cur.fetchall()
    companyName = [i['education'] for i in datas]
    counts = [i['count'] for i in datas]

    data2 = []
    for i in range(len(companyName)):
        data2.append({'name': companyName[i], 'value': counts[i]})
    data = {'data': data2}
    print(data)
    cur.close()
    conn.close()
    return data


if __name__ == '__main__':
    get_education()
